package com.example.demo.service.impl;

import com.example.demo.dao.NoticeDao;
import com.example.demo.entity.Notice;
import com.example.demo.service.NoticeService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Service
public class NoticeServiceImpl implements NoticeService {
    @Resource
 NoticeDao noticeDao;
    private final static String XLS="xls";
    private final static String XLSX = "xlsx";
    @Override
    public int insertnotice(Notice notice) {
        System.out.println(notice);
         return  noticeDao.insertnotice(notice);

    }

    @Override
    public List<Notice> selectAll() {
        return noticeDao.selectAll();
    }

    @Override
    public Notice selectid(int id) {
        return noticeDao.selectid(id);
    }

    @Override
    public int updateid(Notice notice) {
        System.out.println(notice);
          noticeDao.updateid(notice);
          return 1;
    }
    @Override
    public int deleteid(int id) {
        noticeDao.deleteid(id);
        return 1;
    }






    @Override
    public void importExcel(MultipartFile myfile) throws Exception{
        Workbook workbook = null;
        String fileName = myfile.getOriginalFilename();
        if (fileName.endsWith("xls")){
            //2003
            workbook = new HSSFWorkbook(myfile.getInputStream());
        }else if (fileName.endsWith("xlsx")){
            //2007
            workbook = new XSSFWorkbook(myfile.getInputStream());
        }else {
            throw new Exception("文件不是Excel文件");
        }

        Sheet sheet = workbook.getSheet("Sheet1");
        int rows = sheet.getLastRowNum();//行数
        if (rows == 0){
            throw  new Exception("请填写数据");
        }
        for (int i = 1; i <= rows+1 ; i++) {
            Row row = sheet.getRow(i);
            if (row !=null){
                Notice student = new Notice();
                String notice_title = getCellValue(row.getCell(0));
                student.setNotice_title(notice_title);
                String notice_content = getCellValue(row.getCell(1));
                student.setNotice_content(notice_content);

                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");


                noticeDao.insertnotice(student);
            }
        }


    }

    @Override
    public void exportExcel(HttpServletResponse response) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell(0);
        cell.setCellValue("通知标题");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("通知内容");
        cell.setCellStyle(style);
//        cell = row.createCell(2);
//        cell.setCellValue("分数");
//        cell.setCellStyle(style);


        List<Notice> list = noticeDao.selectAll();
        for (int i = 0; i < list.size() ; i++) {
            row = sheet.createRow(i+1);
            Notice stu = list.get(i);
            row.createCell(0).setCellValue(stu.getNotice_title());
            row.createCell(1).setCellValue(stu.getNotice_content());
//            row.createCell(2).setCellValue(stu.getScore());
            cell = row.createCell(2);


            OutputStream output = response.getOutputStream();
            response.reset();
            long filename = System.currentTimeMillis();
            SimpleDateFormat df = new SimpleDateFormat("yyMMddHHmmss");
            String fileName = df.format(new Date());
            response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls");
            response.setContentType("application/msexcel");
            wb.write(output);
            //   output.close();

        }

    }

    private String getCellValue(Cell cell){
        String value = "";
        if (cell != null){
            switch (cell.getCellType()){
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)){
                        Date date = cell.getDateCellValue();
                        if (date != null){
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        }else {
                            value = "";
                        }
                    }else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue()+"";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula()+ "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    value ="";
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;

            }
        }
        return value.trim();
    }
}
